﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Net;
using System.Web;
using System.Windows.Forms;
using Utilities;
using roncliProductions.LibWowAPI;
using roncliProductions.LibWowAPI.Auction;
using roncliProductions.LibWowAPI.Character;
using roncliProductions.LibWowAPI.Enums;
using roncliProductions.LibWowAPI.Realm;
using Appearance = roncliProductions.LibWowAPI.Character.Appearance;
using Profession = roncliProductions.LibWowAPI.Character.Profession;
using Realm = roncliProductions.LibWowAPI.Realm.Realm;

namespace DataCollector
{
    public partial class Main : Form
    {
        private Dictionary<string, string> _notFoundRealms = new Dictionary<string, string>();
        private int _numberOfLookups = 1;
        private BackgroundWorker _realmProgress;
        private string _selectedAction = "";
        private string _selectedConnectionString = "";
        private string _selectedRegion = "";
        private bool _stop;

        public Main()
        {
            InitializeComponent();

            WowAPIData.Application = "Crazy Wow Stats";
            WowAPIData.ApplicationURL = "http://www.crazywowstats.com";
        }

        private void BtnGetDataClick(object sender, EventArgs e)
        {
            _stop = false;
            _selectedAction = cmbAction.Text;
            _selectedRegion = cmbRegion.Text;
            _selectedConnectionString = cmbConnectionString.Text;

            _realmProgress = new BackgroundWorker {WorkerReportsProgress = true};
            _realmProgress.ProgressChanged += RealmProgressProgressChanged;
            _realmProgress.DoWork += RealmProgressDoWork;
            _realmProgress.RunWorkerAsync();
        }

        private void RealmProgressProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            lblStatus.Text = e.UserState.ToString();
        }

        private void RealmProgressDoWork(object sender, DoWorkEventArgs e)
        {
            switch (_selectedAction)
            {
                case "GetRealms":
                    GetRealms();
                    break;
                case "GetAuctions":
                    GetAuctionData();
                    break;
                case "GetCharacter":
                    GetCharacterData();
                    break;
            }
        }

        private void GetRealms()
        {
            SetRegion(_selectedRegion);

            int progressCounter = 1;

            var sqlConnection = new SqlConnection(_selectedConnectionString);
            sqlConnection.Open();

            var realmStatus = new RealmStatus();
            realmStatus.Load();
            int realmsCount = realmStatus.Realms.Count;

            foreach (Realm realm in realmStatus.Realms)
            {
                string name = realm.Name;
                string region = _selectedRegion;
                string type = realm.Type.ToString();
                string slug = realm.Slug;
                string battleGroup = realm.Battlegroup;

                _realmProgress.ReportProgress(1, "Inserting realm: " + realm.Name + " - " + progressCounter + "/" + realmsCount);

                var sqlCommand = new SqlCommand("INSERT INTO Realm (Name, Region, [Type], Slug, BattleGroup) VALUES ('" + HttpUtility.UrlEncode(name) + "', '" + region + "' ,'" + type + "' , '" + slug + "' , '" + battleGroup + "')", sqlConnection);
                sqlCommand.ExecuteNonQuery();
                progressCounter++;
            }
            sqlConnection.Close();
        }

        private void GetAuctionData()
        {
            //SetRegion(_selectedRegion);

            //var sqlConnection = new SqlConnection(_selectedConnectionString);
            //sqlConnection.Open();

            //var sqlConnection1 = new SqlConnection(_selectedConnectionString);
            //sqlConnection1.Open();

            //var sqlCommand = new SqlCommand("SELECT * FROM Realm WHERE Region='" + _selectedRegion + "' ORDER BY Name", sqlConnection);
            //SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
            //while (sqlDataReader.Read())
            //{
            //    string realmName = HttpUtility.UrlDecode(sqlDataReader["Name"].ToString());
            //    string realmId = sqlDataReader["RealmId"].ToString();

            //    GetAuctionOwner(realmId, sqlConnection1, realmName);
            //}
            //sqlDataReader.Close();
            //sqlConnection.Close();

            //while (_notFoundRealms.Count > 0)
            //{
            //    var tmpNotFoundRealms = new Dictionary<string, string>();
            //    foreach (var notFoundRealm in _notFoundRealms)
            //    {
            //        bool found = GetAuctionOwner(notFoundRealm.Key, sqlConnection1, notFoundRealm.Value);
            //        if (!found)
            //        {
            //            tmpNotFoundRealms.Add(notFoundRealm.Key, notFoundRealm.Value);
            //        }
            //    }
            //    _notFoundRealms = tmpNotFoundRealms;
            //}

            //sqlConnection1.Close();

            //MessageBox.Show("Done");
        }

        private bool GetAuctionOwner(string realmId, string characterId, SqlConnection sqlConnection1, string realmName)
        {
            try
            {
                var auctionData = new AuctionData {Options = {Realm = realmName}, CacheLength = new TimeSpan(0, 0, 1), CacheResults = false};
                auctionData.Clear();
                auctionData.Load();

                Collection<Auctions> auctions = auctionData.Auctions;
                foreach (Auctions auction in auctions)
                {
                    if (auction.Alliance != null)
                    {
                        foreach (Auction allianceAuction in auction.Alliance.Auctions)
                        {
                            _realmProgress.ReportProgress(1, "Retrieving character: " + allianceAuction.Owner + "  from - " + realmName);

                            var sqlCommand1 = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection1, CommandText = "InsertCharacter"};
                            sqlCommand1.Parameters.Clear();
                            sqlCommand1.Parameters.AddWithValue("Name", allianceAuction.Owner);
                            sqlCommand1.Parameters.AddWithValue("Level", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Class", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Race", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Gender", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("AchievementPoints", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Thumbnail", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("GuildId", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("RealmId", realmId);
                            sqlCommand1.Parameters.AddWithValue("SideId", "1");
                            sqlCommand1.Parameters.AddWithValue("LastModified", DBNull.Value);

                            sqlCommand1.ExecuteScalar();
                        }
                    }
                    if (auction.Horde != null)
                    {
                        foreach (Auction hordeAuction in auction.Horde.Auctions)
                        {
                            _realmProgress.ReportProgress(1, "Retrieving character: " + hordeAuction.Owner + "  from - " + realmName);

                            var sqlCommand1 = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection1, CommandText = "InsertCharacter"};
                            sqlCommand1.Parameters.Clear();
                            sqlCommand1.Parameters.AddWithValue("Name", hordeAuction.Owner);
                            sqlCommand1.Parameters.AddWithValue("Level", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Class", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Race", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Gender", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("AchievementPoints", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Thumbnail", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("GuildId", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("RealmId", realmId);
                            sqlCommand1.Parameters.AddWithValue("SideId", "2");
                            sqlCommand1.Parameters.AddWithValue("LastModified", DBNull.Value);

                            sqlCommand1.ExecuteScalar();
                        }
                    }
                    if (auction.Neutral != null)
                    {
                        foreach (Auction neutralAuction in auction.Neutral.Auctions)
                        {
                            _realmProgress.ReportProgress(1, "Retrieving character: " + neutralAuction.Owner + "  from - " + realmName);

                            var sqlCommand1 = new SqlCommand {CommandType = CommandType.StoredProcedure, Connection = sqlConnection1, CommandText = "InsertCharacter"};
                            sqlCommand1.Parameters.Clear();
                            sqlCommand1.Parameters.AddWithValue("Name", neutralAuction.Owner);
                            sqlCommand1.Parameters.AddWithValue("Level", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Class", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Race", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Gender", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("AchievementPoints", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("Thumbnail", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("GuildId", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("RealmId", realmId);
                            sqlCommand1.Parameters.AddWithValue("SideId", DBNull.Value);
                            sqlCommand1.Parameters.AddWithValue("LastModified", DBNull.Value);

                            sqlCommand1.ExecuteScalar();
                        }
                    }
                }
            }
            catch (Exception)
            {
                if (!_notFoundRealms.ContainsKey(realmId))
                {
                    _notFoundRealms.Add(realmId, realmName);
                }
                return false;
            }
            return true;
        }

        private void GetCharacterData()
        {
            DateTime startTime = DateTime.Now;
            var charactersToDelete = new List<string>();
            var sqlConnection = new SqlConnection(_selectedConnectionString);
            sqlConnection.Open();

            var sqlCommand = new SqlCommand("SELECT * FROM CharacterView WHERE [Level] IS NULL AND [Region]='" + _selectedRegion + "' ORDER BY [Region]", sqlConnection);
            sqlCommand.CommandTimeout = 600;
            SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
            while (sqlDataReader.Read())
            {
                if (_stop)
                {
                    sqlCommand.Cancel();
                    break;
                }
                string characterId = sqlDataReader["CharacterId"].ToString();
                string characterName = sqlDataReader["CharacterName"].ToString();
                string realmName = sqlDataReader["RealmName"].ToString();
                string realmId = sqlDataReader["RealmId"].ToString();
                string realmSlug = sqlDataReader["Slug"].ToString();
                string region = sqlDataReader["Region"].ToString();

                try
                {
                    SetRegion(region.Trim());
                    _realmProgress.ReportProgress(1, "Number of lookup: " + _numberOfLookups + " - Character: " + characterName + "  from - " + realmName);

                    var characterProfile = new CharacterProfile {Options = {Achievements = true, Appearance = true, Companions = true, Guild = true, Items = true, Mounts = true, Pets = true, Professions = true, Progression = true, PvP = true, Quests = true, Reputation = true, Stats = true, Talents = true, Titles = true, Realm = realmSlug, Name = characterName}};
                    characterProfile.Load();
                    Character character = characterProfile.Character;
                    string sideId = (character.Race.Side == Side.Alliance ? "1" : "2");

                    var sqlConnection1 = new SqlConnection(_selectedConnectionString);
                    sqlConnection1.Open();

                    InsertCharacterAchievements(character.Achievements, characterId, sqlConnection1);
                    InsertCharacterAppearance(character.Appearance, characterId, sqlConnection1);
                    InsertCharacterCompanions(character.Companions, characterId, sqlConnection1);
                    InsertCharacterEquipment(character.Items, characterId, sqlConnection1);
                    InsertCharacterMounts(character.Mounts, characterId, sqlConnection1);
                    InsertCharacterPets(character.Pets, characterId, sqlConnection1);
                    InsertCharacterProfessions(character.Professions, characterId, sqlConnection1);
                    if (character.Level > 55)
                    {
                        InsertCharacterProgression(character.Progression, characterId, sqlConnection1);
                    }
                    InsertCharacterPvp(character.PvP, characterId, realmId, sqlConnection1);
                    InsertCharacterQuests(character.Quests, characterId, sqlConnection1);
                    InsertCharacterReputation(character.Reputation, characterId, sqlConnection1);
                    InsertCharacterStats(character.Stats, characterId, sqlConnection1);
                    InsertCharacterTalent(character.Talents, characterId, sqlConnection1);
                    InsertCharacterTitles(character.Titles, characterId, sqlConnection1);
                    string guildId = InsertCharacterGuild(character.Guild, realmId, sideId, sqlConnection1);
                    InsertCharacterBaseData(character, guildId, sideId, realmId, character.PvP, sqlConnection1);

                    sqlConnection1.Close();

                    _numberOfLookups++;
                }
                catch (WebException exception)
                {
                    if (exception.Message == "The remote server returned an error: (503) Server Unavailable.")
                    {
                        sqlCommand.Cancel();
                        break;
                    }
                    else
                    {
                        _numberOfLookups++;
                        charactersToDelete.Add(characterId);
                    }

                    /*if (exception.Message == "The remote server returned an error: (404) Not Found.")
                    {
                        _numberOfLookups++;
                        charactersToDelete.Add(characterId);
                    }
                    else
                    {
                        MessageBox.Show(exception.Message + Environment.NewLine + exception.StackTrace);
                    }*/
                }
                catch (Exception exception)
                {
                    _numberOfLookups++;
                    charactersToDelete.Add(characterId);
                    /*if (!exception.Message.Contains("Timeout expired."))
                    {
                        MessageBox.Show(exception.Message + Environment.NewLine + exception.StackTrace);
                    }*/
                }
            }
            sqlDataReader.Close();

            if (charactersToDelete.Count > 0)
            {
                _realmProgress.ReportProgress(1, "Deleting " + charactersToDelete.Count + " characters.");
            }

            foreach (string characterId in charactersToDelete)
            {
                sqlCommand = new SqlCommand("DELETE FROM Character WHERE CharacterId='" + characterId + "'", sqlConnection);
                sqlCommand.ExecuteNonQuery();
            }

            sqlConnection.Close();

            DateTime endTime = DateTime.Now;
            File.AppendAllText("character.log", "Start time: " + startTime.ToShortDateString() + "-" + startTime.ToLongTimeString() + " -- End time: " + endTime.ToShortDateString() + "-" + endTime.ToLongTimeString() + " -- Number of lookups: " + _numberOfLookups + " -- Characters not found: " + charactersToDelete.Count + Environment.NewLine);
            MessageBox.Show("Done");
        }

        private void InsertCharacterAchievements(Achievements achievements, string characterId, SqlConnection sqlConnection)
        {
            if (achievements != null)
            {
                string completedAchievements;
                string completedTimeStamps;
                ConvertAchievementsToString(achievements, out completedAchievements, out completedTimeStamps);

                string json = "{characterId:\"" + characterId + "\",completedAchievements:\"" + completedAchievements + "\",completedTimeStamps:\"" + completedTimeStamps + "\"}";

                Database.InsertAchievements(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterAppearance(Appearance appearance, string characterId, SqlConnection sqlConnection)
        {
            if (appearance != null)
            {
                string json = "{characterId:\"" + characterId + "\",faceVariation:\"" + appearance.FaceVariation + "\",featureVariation:\"" + appearance.FeatureVariation + "\",hairColor:\"" + appearance.HairColor + "\",hairVariation:\"" + appearance.HairVariation + "\",showCloak:\"" + appearance.ShowCloak + "\",showHelm:\"" + appearance.ShowHelm + "\",skinColor:\"" + appearance.SkinColor + "\"}";


                Database.InsertAppearance(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterCompanions(IEnumerable<int> companions, string characterId, SqlConnection sqlConnection)
        {
            if (companions != null)
            {
                string companionsAsString = ConvertListToString(companions);
                string json = "{characterId:\"" + characterId + "\",companionsAsString:\"" + companionsAsString + "\"}";


                Database.InsertCompanions(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterEquipment(Items items, string characterId, SqlConnection sqlConnection)
        {
            if (items != null)
            {
                string itemEnhancementJson = "{\"characterId\":\"" + characterId + "\",";
                string itemJson = "{\"characterId\":\"" + characterId + "\",";

                if (items.Back != null)
                {
                    itemJson += "\"back\":" + GetItemJson(items.Back) + ",";
                    if (GotItemEnhancement(items.Back))
                    {
                        itemEnhancementJson += "\"back\":" + GetItemEnhancementJson(items.Back) + ",";
                    }
                }

                if (items.Chest != null)
                {
                    itemJson += "\"chest\":" + GetItemJson(items.Chest) + ",";
                    if (GotItemEnhancement(items.Chest))
                    {
                        itemEnhancementJson += "\"chest\":" + GetItemEnhancementJson(items.Chest) + ",";
                    }
                }

                if (items.Feet != null)
                {
                    itemJson += "\"feet\":" + GetItemJson(items.Feet) + ",";
                    if (GotItemEnhancement(items.Feet))
                    {
                        itemEnhancementJson += "\"feet\":" + GetItemEnhancementJson(items.Feet) + ",";
                    }
                }

                if (items.Finger1 != null)
                {
                    itemJson += "\"finger1\":" + GetItemJson(items.Finger1) + ",";
                    if (GotItemEnhancement(items.Finger1))
                    {
                        itemEnhancementJson += "\"finger1\":" + GetItemEnhancementJson(items.Finger1) + ",";
                    }
                }

                if (items.Finger2 != null)
                {
                    itemJson += "\"finger2\":" + GetItemJson(items.Finger2) + ",";
                    if (GotItemEnhancement(items.Finger2))
                    {
                        itemEnhancementJson += "\"finger2\":" + GetItemEnhancementJson(items.Finger2) + ",";
                    }
                }

                if (items.Hands != null)
                {
                    itemJson += "\"hands\":" + GetItemJson(items.Hands) + ",";
                    if (GotItemEnhancement(items.Hands))
                    {
                        itemEnhancementJson += "\"hands\":" + GetItemEnhancementJson(items.Hands) + ",";
                    }
                }

                if (items.Head != null)
                {
                    itemJson += "\"head\":" + GetItemJson(items.Head) + ",";
                    if (GotItemEnhancement(items.Head))
                    {
                        itemEnhancementJson += "\"head\":" + GetItemEnhancementJson(items.Head) + ",";
                    }
                }

                if (items.Legs != null)
                {
                    itemJson += "\"legs\":" + GetItemJson(items.Legs) + ",";
                    if (GotItemEnhancement(items.Legs))
                    {
                        itemEnhancementJson += "\"legs\":" + GetItemEnhancementJson(items.Legs) + ",";
                    }
                }

                if (items.MainHand != null)
                {
                    itemJson += "\"mainHand\":" + GetItemJson(items.MainHand) + ",";
                    if (GotItemEnhancement(items.MainHand))
                    {
                        itemEnhancementJson += "\"mainHand\":" + GetItemEnhancementJson(items.MainHand) + ",";
                    }
                }

                if (items.Neck != null)
                {
                    itemJson += "\"neck\":" + GetItemJson(items.Neck) + ",";
                    if (GotItemEnhancement(items.Neck))
                    {
                        itemEnhancementJson += "\"neck\":" + GetItemEnhancementJson(items.Neck) + ",";
                    }
                }

                if (items.OffHand != null)
                {
                    itemJson += "\"offHand\":" + GetItemJson(items.OffHand) + ",";
                    if (GotItemEnhancement(items.OffHand))
                    {
                        itemEnhancementJson += "\"offHand\":" + GetItemEnhancementJson(items.OffHand) + ",";
                    }
                }

                if (items.Ranged != null)
                {
                    itemJson += "\"ranged\":" + GetItemJson(items.Ranged) + ",";
                    if (GotItemEnhancement(items.Ranged))
                    {
                        itemEnhancementJson += "\"ranged\":" + GetItemEnhancementJson(items.Ranged) + ",";
                    }
                }

                if (items.Shirt != null)
                {
                    itemJson += "\"shirt\":" + GetItemJson(items.Shirt) + ",";
                    if (GotItemEnhancement(items.Shirt))
                    {
                        itemEnhancementJson += "\"shirt\":" + GetItemEnhancementJson(items.Shirt) + ",";
                    }
                }

                if (items.Shoulder != null)
                {
                    itemJson += "\"shoulder\":" + GetItemJson(items.Shoulder) + ",";
                    if (GotItemEnhancement(items.Shoulder))
                    {
                        itemEnhancementJson += "\"shoulder\":" + GetItemEnhancementJson(items.Shoulder) + ",";
                    }
                }

                if (items.Tabard != null)
                {
                    itemJson += "\"tabard\":" + GetItemJson(items.Tabard) + ",";
                    if (GotItemEnhancement(items.Tabard))
                    {
                        itemEnhancementJson += "\"tabard\":" + GetItemEnhancementJson(items.Tabard) + ",";
                    }
                }

                if (items.Trinket1 != null)
                {
                    itemJson += "\"trinket1\":" + GetItemJson(items.Trinket1) + ",";
                    if (GotItemEnhancement(items.Trinket1))
                    {
                        itemEnhancementJson += "\"trinket1\":" + GetItemEnhancementJson(items.Trinket1) + ",";
                    }
                }

                if (items.Trinket2 != null)
                {
                    itemJson += "\"trinket2\":" + GetItemJson(items.Trinket2) + ",";
                    if (GotItemEnhancement(items.Trinket2))
                    {
                        itemEnhancementJson += "\"trinket2\":" + GetItemEnhancementJson(items.Trinket2) + ",";
                    }
                }

                if (items.Waist != null)
                {
                    itemJson += "\"waist\":" + GetItemJson(items.Waist) + ",";
                    if (GotItemEnhancement(items.Waist))
                    {
                        itemEnhancementJson += "\"waist\":" + GetItemEnhancementJson(items.Waist) + ",";
                    }
                }

                if (items.Wrist != null)
                {
                    itemJson += "\"wrist\":" + GetItemJson(items.Wrist);
                    if (GotItemEnhancement(items.Wrist))
                    {
                        itemEnhancementJson += "\"wrist\":" + GetItemEnhancementJson(items.Wrist) + ",";
                    }
                }

                itemJson += "}";
                itemEnhancementJson += "}";

                Database.InsertItems(Misc.Compress(itemJson), sqlConnection);
                Database.InsertItemEnhancements(Misc.Compress(itemEnhancementJson), sqlConnection);

                string json = "{characterId:\"" + characterId + "\",averageItemLevel:\"" + items.AverageItemLevel + "\",averageItemLevelEquipped:\"" + items.AverageItemLevelEquipped + "\",backItemId:\"" + (items.Back != null ? items.Back.ID.ToString() : "") + "\",chestItemId:\"" + (items.Chest != null ? items.Chest.ID.ToString() : "") + "\",feetItemId:\"" + (items.Feet != null ? items.Feet.ID.ToString() : "") + "\",finger1ItemId:\"" + (items.Finger1 != null ? items.Finger1.ID.ToString() : "") + "\",finger2ItemId:\"" + (items.Finger2 != null ? items.Finger2.ID.ToString() : "") + "\",handsItemId:\"" + (items.Hands != null ? items.Hands.ID.ToString() : "") + "\",headItemId:\"" + (items.Head != null ? items.Head.ID.ToString() : "") + "\",legsItemId:\"" + (items.Legs != null ? items.Legs.ID.ToString() : "") + "\",mainHandItemId:\"" + (items.MainHand != null ? items.MainHand.ID.ToString() : "") + "\",neckItemId:\"" + (items.Neck != null ? items.Neck.ID.ToString() : "") + "\",offHandItem:\"" + (items.OffHand != null ? items.OffHand.ID.ToString() : "") + "\",rangedItemId:\"" + (items.Ranged != null ? items.Ranged.ID.ToString() : "") + "\",shirtItemId:\"" + (items.Shirt != null ? items.Shirt.ID.ToString() : "") + "\",shouldItemId:\"" + (items.Shoulder != null ? items.Shoulder.ID.ToString() : "") + "\",tabardItemId:\"" + (items.Tabard != null ? items.Tabard.ID.ToString() : "") + "\",trinket1ItemId:\"" + (items.Trinket1 != null ? items.Trinket1.ID.ToString() : "") + "\",trinket2ItemId:\"" + (items.Trinket2 != null ? items.Trinket2.ID.ToString() : "") + "\",waistItemId:\"" + (items.Waist != null ? items.Waist.ID.ToString() : "") + "\",wristItemId:\"" + (items.Wrist != null ? items.Wrist.ID.ToString() : "") + "\"}";

                Database.InsertEquipment(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterMounts(IEnumerable<int> mounts, string characterId, SqlConnection sqlConnection)
        {
            if (mounts != null)
            {
                string mountsAsString = ConvertListToString(mounts);
                string json = "{characterId:\"" + characterId + "\",mounts:\"" + mountsAsString + "\"}";


                Database.InsertMounts(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterPets(IEnumerable<Pet> pets, string characterId, SqlConnection sqlConnection)
        {
            if (pets != null)
            {
                int numberOfPets = 0;
                string json = "{\"characterId\":\"" + characterId + "\",\"pets\":[";

                foreach (Pet pet in pets)
                {
                    string petId = pet.Creature.ToString(CultureInfo.InvariantCulture);
                    string petName = pet.Name;
                    string talentBuild = (pet.Talents != null ? pet.Talents.Build : "");
                    json += "{\"petId\":\"" + petId + "\",\"talentBuild\":\"" + talentBuild + "\",\"name\":\"" + petName + "\"},";
                    numberOfPets++;
                }
                if (json.EndsWith(","))
                {
                    json = json.Substring(0, json.Length - 1);
                }
                json += "],\"numberOfPets\":" + numberOfPets + "}";


                Database.InsertPets(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterProfessions(Professions professions, string characterId, SqlConnection sqlConnection)
        {
            if (professions != null)
            {
                int numberOfProfessions = 0;
                string json = "{characterId:\"" + characterId + "\"}";

                Database.DeleteProfessions(Misc.Compress(json), sqlConnection);

                json = "{\"characterId\":\"" + characterId + "\",\"professions\":[";

                if (professions.Primary != null)
                {
                    foreach (Profession profession in professions.Primary)
                    {
                        json += "{\"rank\":\"" + profession.Rank + "\",\"max\":\"" + profession.Max + "\",\"recipies\":\"" + ConvertListToString(profession.Recipes) + "\",\"name\":\"" + profession.Profession + "\"},";
                        numberOfProfessions++;
                    }
                }

                if (professions.Secondary != null)
                {
                    foreach (Profession profession in professions.Secondary)
                    {
                        json += "{\"rank\":\"" + profession.Rank + "\",\"max\":\"" + profession.Max + "\",\"recipies\":\"" + ConvertListToString(profession.Recipes) + "\",\"name\":\"" + profession.Profession + "\"},";
                        numberOfProfessions++;
                    }
                }
                if (json.EndsWith(","))
                {
                    json = json.Substring(0, json.Length - 1);
                }
                json += "],\"numberOfProfessions\":" + numberOfProfessions + "}";
                Database.InsertProfessions(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterProgression(Progression progression, string characterId, SqlConnection sqlConnection)
        {
            if (progression != null)
            {
                int numberOfInstances = 0;
                int numberOfBosses = 0;
                string instanceJson = "{\"instances\":[";
                string bossJson = "{\"characterId\":\"" + characterId + "\",\"bosses\":[";

                foreach (Instance instance in progression.Raids)
                {
                    instanceJson += "{\"instanceId\":\"" + instance.ID + "\",\"name\":\"" + instance.Name + "\"},";
                    numberOfInstances++;

                    foreach (Boss boss in instance.Bosses)
                    {
                        if (boss.NormalKills > 0 || boss.HeroicKills > 0)
                        {
                            bossJson += "{\"raidBossId\":\"" + boss.ID + "\",\"raidBossName\":\"" + boss.Name + "\",\"instanceId\":\"" + instance.ID + "\",\"characterId\":\"" + characterId + "\",\"normalKills\":\"" + boss.NormalKills + "\",\"heroicKills\":\"" + boss.HeroicKills + "\"},";
                            numberOfBosses++;
                        }
                    }
                }

                if (instanceJson.EndsWith(","))
                {
                    instanceJson = instanceJson.Substring(0, instanceJson.Length - 1);
                }

                if (bossJson.EndsWith(","))
                {
                    bossJson = bossJson.Substring(0, bossJson.Length - 1);
                }
                instanceJson += "],\"numberOfInstances\":" + numberOfInstances + "}";
                bossJson += "],\"numberOfBosses\":" + numberOfBosses + "}";


                Database.InsertRaidInstances(Misc.Compress(instanceJson), sqlConnection);
                Database.InsertProgression(Misc.Compress(bossJson), sqlConnection);
            }
        }

        private void InsertCharacterPvp(PvP pvp, string characterId, string realmId, SqlConnection sqlConnection)
        {
            if (pvp != null)
            {
                int numberOfBattlegrounds = 0;
                int numberOfArenaTeams = 0;

                // Insert battlegrounds
                string json = "{\"characterId\":\"" + characterId + "\",\"battlegrounds\":[";
                foreach (Battleground battleground in pvp.RatedBattlegrounds.Battlegrounds)
                {
                    json += "{\"battlegroundName\":\"" + battleground.Name + "\",\"played\":\"" + battleground.Played + "\",\"won\":\"" + battleground.Won + "\"},";
                    numberOfBattlegrounds++;
                }
                if (json.EndsWith(","))
                {
                    json = json.Substring(0, json.Length - 1);
                }
                json += "],\"numberOfBattlegrounds\":" + numberOfBattlegrounds + "}";
                Database.InsertBattlegrounds(Misc.Compress(json), sqlConnection);

                // Delete arena teams
                json = "{\"characterId\":\"" + characterId + "\"}";
                Database.DeleteArenaTeams(Misc.Compress(json), sqlConnection);

                // Insert areana teams
                json = "{\"characterId\":\"" + characterId + "\",\"arenaTeams\":[";
                foreach (ArenaTeam arenaTeam in pvp.ArenaTeams)
                {
                    json += "{\"arenaTeamName\":\"" + arenaTeam.Name.Replace("\\", "\\\\").Replace("\"", "\\\"") + "\",\"arenaTeamSize\":\"" + arenaTeam.Size + "\",\"arenaTeamRating\":\"" + arenaTeam.TeamRating + "\",\"realmId\":\"" + realmId + "\",\"arenaTeamPersonalRating\":\"" + arenaTeam.PersonalRating + "\"},";
                    numberOfArenaTeams++;
                }
                if (json.EndsWith(","))
                {
                    json = json.Substring(0, json.Length - 1);
                }
                json += "],\"numberOfArenaTeams\":" + numberOfArenaTeams + "}";
                Database.InsertArenaTeams(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterQuests(IEnumerable<int> quests, string characterId, SqlConnection sqlConnection)
        {
            if (quests != null)
            {
                string completedQuests = ConvertListToString(quests);
                string json = "{\"characterId\":\"" + characterId + "\",\"completedQuests\":\"" + completedQuests + "\"}";

                Database.InsertQuests(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterReputation(IEnumerable<Reputation> reputations, string characterId, SqlConnection sqlConnection)
        {
            if (reputations != null)
            {
                int numberOfReputations = 0;
                string json = "{\"characterId\":\"" + characterId + "\",\"reputations\":[";
                foreach (Reputation reputation in reputations)
                {
                    if (reputation.Standing > 0 || (reputation.Standing == 0 && reputation.Value > 0))
                    {
                        json += "{\"factionId\":\"" + reputation.ID + "\",\"factionName\":\"" + reputation.Name + "\",\"standing\":\"" + reputation.Standing + "\",\"value\":\"" + reputation.Value + "\",\"max\":\"" + reputation.Max + "\"},";
                        numberOfReputations++;
                    }
                }
                if (json.EndsWith(","))
                {
                    json = json.Substring(0, json.Length - 1);
                }
                json += "],\"numberOfReputations\":" + numberOfReputations + "}";


                Database.InsertReputations(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterStats(Stats stats, string characterId, SqlConnection sqlConnection)
        {
            if (stats != null)
            {
                string json = "{characterId:\"" + characterId + "\",health:\"" + stats.Health + "\",powerType:\"" + stats.PowerType + "\",power:\"" + stats.Power + "\",strength:\"" + stats.Str + "\",agility:\"" + stats.Agi + "\",intellect:\"" + stats.Int + "\",spirit:\"" + stats.Spr + "\",attackPower:\"" + stats.AttackPower + "\",rangedAttackPower:\"" + stats.RangedAttackPower + "\",mastery:\"" + stats.Mastery + "\",masteryRating:\"" + stats.MasteryRating + "\",criticalPct:\"" + stats.Crit + "\",criticalRating:\"" + stats.CritRating + "\",hitPct:\"" + stats.HitPercent + "\",hitRating:\"" + stats.HitRating + "\",hasteRating:\"" + stats.HasteRating + "\",expertiseRating:\"" + stats.ExpertiseRating + "\",spellPower:\"" + stats.SpellPower + "\",spellPenetration:\"" + stats.SpellPen + "\",spellCriticalPct:\"" + stats.SpellCrit + "\",spellCriticalRating:\"" + stats.SpellCritRating + "\",spellHitPct:\"" + stats.SpellHitPercent + "\",spellHitRating:\"" + stats.SpellHitRating + "\",mp5:\"" + stats.Mana5 + "\",mp5Combat:\"" + stats.Mana5Combat + "\",armor:\"" + stats.Armor + "\",dodgePct:\"" + stats.Dodge + "\",dodgeRating:\"" + stats.DodgeRating + "\",parryPct:\"" + stats.Parry + "\",parryRating:\"" + stats.ParryRating + "\",blockPct:\"" + stats.Block + "\",blockRating:\"" + stats.BlockRating + "\",resillience:\"" + stats.Resil + "\",mainHandDmgMin:\"" + stats.MainHandDmgMin + "\",mainHandDmgMax:\"" + stats.MainHandDmgMax + "\",mainHandSpeed:\"" + stats.MainHandSpeed + "\",mainHandDps:\"" + stats.MainHandDps + "\",mainHandExpertise:\"" + stats.MainHandExpertise + "\",offHandDmgMin:\"" + stats.OffHandDmgMin + "\",offHandDmgMax:\"" + stats.OffHandDmgMax + "\",offHandSpeed:\"" + stats.OffHandSpeed + "\",offHandDps:\"" + stats.OffHandDps + "\",offHandExpertise:\"" + stats.OffHandExpertise + "\",rangedDmgMin:\"" + stats.RangedDmgMin + "\",rangedDmgMax:\"" + stats.RangedDmgMax + "\",rangedSpeed:\"" + stats.RangedSpeed + "\",rangedDps:\"" + stats.RangedDps + "\",rangedCritPct:\"" + stats.RangedCrit + "\",rangedCritRating:\"" + stats.RangedCritRating + "\",rangedHitPct:\"" + stats.RangedHitPercent + "\",rangedHitRating:\"" + stats.RangedHitRating + "\",stamina:\"" + stats.Sta + "\"}";


                Database.InsertStats(Misc.Compress(json), sqlConnection);
            }
        }

        private void InsertCharacterTalent(IEnumerable<Talent> talents, string characterId, SqlConnection sqlConnection)
        {
            if (talents != null)
            {
                int numberOfGlyphs = 0;
                int numberOfTalents = 0;
                string glyphJson = "{\"glyphs\":[";
                string talentJson = "{\"characterId\":\"" + characterId + "\",\"talents\":[";

                foreach (Talent talent in talents)
                {
                    string primeGlyph = "";
                    string majorGlyph = "";
                    string minorGlyph = "";

                    foreach (Glyph glyph in talent.Glyphs.Prime)
                    {
                        glyphJson += "{\"glyphId\":\"" + glyph.Glyph + "\",\"itemId\":\"" + glyph.Item + "\",\"icon\":\"" + glyph.Icon + "\",\"name\":\"" + glyph.Name + "\"},";
                        primeGlyph += glyph.Glyph + ",";
                        numberOfGlyphs++;
                    }
                    foreach (Glyph glyph in talent.Glyphs.Major)
                    {
                        glyphJson += "{\"glyphId\":\"" + glyph.Glyph + "\",\"itemId\":\"" + glyph.Item + "\",\"icon\":\"" + glyph.Icon + "\",\"name\":\"" + glyph.Name + "\"},";
                        majorGlyph += glyph.Glyph + ",";
                        numberOfGlyphs++;
                    }
                    foreach (Glyph glyph in talent.Glyphs.Minor)
                    {
                        glyphJson += "{\"glyphId\":\"" + glyph.Glyph + "\",\"itemId\":\"" + glyph.Item + "\",\"icon\":\"" + glyph.Icon + "\",\"name\":\"" + glyph.Name + "\"},";
                        minorGlyph += glyph.Glyph + ",";
                        numberOfGlyphs++;
                    }

                    talentJson += "{\"build\":\"" + talent.Build + "\",\"icon\":\"" + (talent.Icon ?? "") + "\",\"name\":\"" + (talent.Name ?? "") + "\",\"selected\":\"" + talent.Selected + "\",\"primeGlyph\":\"" + primeGlyph + "\",\"majorGlyph\":\"" + majorGlyph + "\",\"minorGlyph\":\"" + minorGlyph + "\"},";
                    numberOfTalents++;
                }
                if (glyphJson.EndsWith(","))
                {
                    glyphJson = glyphJson.Substring(0, glyphJson.Length - 1);
                }
                glyphJson += "],\"numberOfGlyphs\":" + numberOfGlyphs + "}";
                if (talentJson.EndsWith(","))
                {
                    talentJson = talentJson.Substring(0, talentJson.Length - 1);
                }
                talentJson += "],\"numberOfTalents\":" + numberOfTalents + "}";


                Database.InsertGlyphs(Misc.Compress(glyphJson), sqlConnection);
                Database.InsertTalents(Misc.Compress(talentJson), sqlConnection);
            }
        }

        private void InsertCharacterTitles(IEnumerable<Title> titles, string characterId, SqlConnection sqlConnection)
        {
            if (titles != null)
            {
                int numberOfTitles = 0;
                string json = "{\"characterId\":\"" + characterId + "\",\"titles\":[";

                foreach (Title title in titles)
                {
                    json += "{\"titleId\":\"" + title.ID + "\",\"name\":\"" + title.Name + "\",\"selected\":\"" + title.Selected + "\"},";
                    numberOfTitles++;
                }
                if (json.EndsWith(","))
                {
                    json = json.Substring(0, json.Length - 1);
                }
                json += "],\"numberOfTitles\":" + numberOfTitles + "}";


                Database.InsertTitles(Misc.Compress(json), sqlConnection);
            }
        }

        private string InsertCharacterGuild(Guild guild, string realmId, string side, SqlConnection sqlConnection)
        {
            string guildId = "0";
            if (guild != null)
            {
                string json = "{guildName:\"" + guild.Name + "\",realmId:\"" + realmId + "\",level:\"" + guild.Level + "\",members:\"" + guild.Members + "\",side:\"" + side + "\",achievementPoints:\"" + guild.AchievementPoints + "\"}";

                guildId = Database.InsertGuild(Misc.Compress(json), sqlConnection);
            }
            return guildId;
        }

        private void InsertCharacterBaseData(Character character, string guildId, string sideId, string realmId, PvP pvp, SqlConnection sqlConnection)
        {
            if (character != null)
            {
                string json = "{\"name\":\"" + character.Name + "\",\"level\":\"" + character.Level + "\",\"class\":\"" + character.Class.ID + "\",\"race\":\"" + character.Race.ID + "\",\"gender\":\"" + character.Gender + "\",\"achievementPoints\":\"" + character.AchievementPoints + "\",\"thumbnail\":\"" + character.Thumbnail + "\",\"totalHonorableKills\":\"" + (pvp != null ? pvp.TotalHonorableKills : 0) + "\",\"personalBattlegroundRating\":\"" + (pvp != null && pvp.RatedBattlegrounds != null ? pvp.RatedBattlegrounds.PersonalRating : 0) + "\",\"guildId\":\"" + guildId + "\",\"realmId\":\"" + realmId + "\",\"sideId\":\"" + sideId + "\",\"lastModified\":\"" + character.LastModified + "\"}";

                Database.InsertCharacter(Misc.Compress(json), sqlConnection);
            }
        }

        private void ConvertAchievementsToString(Achievements achievements, out string completedAchievements, out string completedTimeStamps)
        {
            completedAchievements = "";
            completedTimeStamps = "";

            foreach (Achievement achievement in achievements.Achievements)
            {
                completedAchievements += achievement.ID + ",";
                var sqlDateTime = new SqlDateTime(achievement.Timestamp);
                completedTimeStamps += sqlDateTime.ToString() + ",";
            }
        }

        private string ConvertListToString(IEnumerable<int> listToConvert)
        {
            return listToConvert.Aggregate("", (current, companion) => current + (companion + ","));
        }

        private string GetItemJson(Item item)
        {
            string itemJson = "{\"itemId\":\"" + item.ID + "\",\"icon\":\"" + item.Icon + "\",\"name\":\"" + item.Name.Replace("\"", "'") + "\",\"quality\":\"" + item.Quality + "\"}";
            return itemJson;
        }

        private string GetItemEnhancementJson(Item item)
        {
            string itemEnhancementJson = "{\"itemId\":\"" + item.ID + "\",\"enchant\":\"" + item.TooltipParams.Enchant + "\",\"extraSocket\":\"" + item.TooltipParams.ExtraSocket + "\",\"gem0\":\"" + (item.TooltipParams.Gems.Count >= 1 ? item.TooltipParams.Gems[0] : 0) + "\",\"gem1\":\"" + (item.TooltipParams.Gems.Count >= 2 ? item.TooltipParams.Gems[1] : 0) + "\",\"gem2\":\"" + (item.TooltipParams.Gems.Count >= 3 ? item.TooltipParams.Gems[2] : 0) + "\",\"reforge\":\"" + item.TooltipParams.Reforge + "\",\"seed\":\"" + item.TooltipParams.Seed + "\",\"set\":\"" + ConvertListToString(item.TooltipParams.Set) + "\",\"suffix\":\"" + item.TooltipParams.Suffix + "\"}";
            return itemEnhancementJson;
        }

        private void SetRegion(string region)
        {
            switch (region)
            {
                case "EU":
                    WowAPIData.Region = roncliProductions.LibWowAPI.Internationalization.Region.Europe;
                    break;
                case "US":
                    WowAPIData.Region = roncliProductions.LibWowAPI.Internationalization.Region.NorthAmerica;
                    break;
            }
        }

        private bool GotItemEnhancement(Item item)
        {
            if (item.TooltipParams.Enchant == 0 && !item.TooltipParams.ExtraSocket && item.TooltipParams.Gems.Count == 0 && item.TooltipParams.Reforge == 0 && item.TooltipParams.Set.Count == 0)
            {
                return false;
            }

            return true;
        }

        private void btnStop_Click(object sender, EventArgs e)
        {
            _stop = true;
        }
    }
}